package com.vcc.service.impl;


import cn.dev33.satoken.stp.StpUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSONObject;
import com.mybatisflex.core.paginate.Page;
import com.mybatisflex.core.query.QueryChain;
import com.mybatisflex.core.query.QueryWrapper;
import com.mybatisflex.core.row.Row;
import com.mybatisflex.spring.service.impl.ServiceImpl;
import com.vcc.core.constant.CardStatusEnum;
import com.vcc.core.exception.NotFoundException;
import com.vcc.core.model.CountData;
import com.vcc.core.util.DateUtil;
import com.vcc.core.util.RegexUtil;
import com.vcc.data.entity.Role;
import com.vcc.data.entity.UserCard;
import com.vcc.data.mapper.UserCardMapper;
import com.vcc.service.boss.response.userCard.UserCardStatisticsVo;
import com.vcc.service.client.vo.userCard.UserCardVo;
import com.vcc.service.sa.BossUserUtil;
import com.vcc.service.service.UserCardService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

import static com.vcc.data.entity.table.BossUserTableDef.BOSS_USER;
import static com.vcc.data.entity.table.CardGroupTableDef.CARD_GROUP;
import static com.vcc.data.entity.table.CardholderTableDef.CARDHOLDER;
import static com.vcc.data.entity.table.UserCardTableDef.USER_CARD;
import static com.vcc.data.entity.table.UserCardWalletTableDef.USER_CARD_WALLET;
import static com.vcc.data.entity.table.UserTableDef.USER;

@Service
public class UserCardServiceImpl extends ServiceImpl<UserCardMapper, UserCard> implements UserCardService {
    @Override
    public UserCard getById(Long id) {
        return queryChain().eq(UserCard::getId, id).oneOpt().orElseThrow(() -> new NotFoundException("没有当前卡信息"));
    }

    @Override
    public UserCard getByCardNumber(String cardNumber) {
        if (cardNumber.length() == 4) {
            return queryChain().eq(UserCard::getCardNumberLastFour, (cardNumber)).oneOpt().orElseThrow(() -> new NotFoundException("没有当前卡号"));
        } else {
            return queryChain().eq(UserCard::getCardNumber, (cardNumber)).oneOpt().orElseThrow(() -> new NotFoundException("没有当前卡号"));
        }
    }

    @Override
    public UserCard getByOrderId(String orderId) {
        return queryChain().eq(UserCard::getOrderId, orderId).oneOpt().orElseThrow(() -> new NotFoundException("没有当前订单号对应的卡信息"));
    }

    @Override
    public UserCard getByChannelCardId(String channelCardId) {
        return queryChain().eq(UserCard::getChannelCardId, channelCardId).oneOpt().orElseThrow(() -> new NotFoundException("没有这张卡"));
    }

    @Override
    public UserCard getByChannelCardIdOrNullAndEmpty(String channelCardId) {
        return queryChain().eq(UserCard::getChannelCardId, channelCardId).oneOpt().orElse(null);
    }

    @Override
    public Boolean existsByChannelCardId(String channelCardId) {
        return queryChain().eq(UserCard::getChannelCardId, channelCardId).exists();
    }

    @Override
    public Boolean updateRemarkById(Long id, String remark) {
        return updateChain().set(UserCard::getRemark, RegexUtil.checkStringIsNull(remark) ? "" : remark).eq(UserCard::getId, id).update();
    }

    @Override
    public Page<UserCard> paging(
            Page<UserCard> paging,
            Long targetUserId,
            String cardNumberLastFour,
            String remark,
            String startDate,
            String endDate,
            String cardStatus,
            Long id,
            String sortMap,
            Long cardTypeId,
            String channelCode) {
        QueryChain<UserCard> queryChain = queryChain();
        queryChain.leftJoin(USER).on(USER_CARD.USER_ID.eq(USER.ID));
        queryChain.leftJoin(BOSS_USER.as("dealer")).on(USER.BE_LONG_TO_DEALER.eq(BOSS_USER.ID));
        queryChain.leftJoin(CARDHOLDER).on(USER_CARD.CARDHOLDER_ID.eq(CARDHOLDER.ID));
        queryChain.select(USER_CARD.ALL_COLUMNS);
        queryChain.select(USER.ACCOUNT.as("account"));
        queryChain.select(BOSS_USER.as("dealer").ACCOUNT.as("dealerAccount"));
        queryChain.select(CARDHOLDER.FIRST_NAME.as("firstName"), CARDHOLDER.LAST_NAME.as("lastName"));

        Long bossUserId = BossUserUtil.getIdNotNull();
        if (!BossUserUtil.hasAllData()) {
            queryChain.and(
                    BOSS_USER.as("dealer").ID.eq(bossUserId)
                            .or(BOSS_USER.as("dealer").PARENT_ID.eq(bossUserId)));
        }

        if (RegexUtil.checkObjectIsNotNull(targetUserId)) {
            queryChain.eq(UserCard::getUserId, targetUserId);
        }
        if (RegexUtil.checkStringIsNotNull(cardNumberLastFour)) {
            if (cardNumberLastFour.length() == 4) {
                queryChain.eq(UserCard::getCardNumberLastFour, (cardNumberLastFour));
            } else {
                queryChain.eq(UserCard::getCardNumber, (cardNumberLastFour));
            }
        }
        if (RegexUtil.checkStringIsNotNull(remark)) {
            queryChain.like(UserCard::getRemark, remark);
        }
        if (RegexUtil.checkObjectIsNotNull(startDate)) {
            queryChain.ge(UserCard::getRequestDate, DateUtil.getDayBeginDate(startDate, "yyyy-MM-dd"));
        }
        if (RegexUtil.checkObjectIsNotNull(endDate)) {
            queryChain.le(UserCard::getRequestDate, DateUtil.getDayEndDate(endDate, "yyyy-MM-dd"));
        }
        if (RegexUtil.checkObjectIsNotNull(id)) {
            queryChain.eq(UserCard::getId, id);
        }
        if (RegexUtil.checkStringIsNotNull(cardStatus)) {
            queryChain.eq(UserCard::getCardStatus, cardStatus);
        } else {
            if (StringUtils.isBlank(cardNumberLastFour)) {
                queryChain.in(UserCard::getCardStatus, CardStatusEnum.ACTIVE.getCode(), CardStatusEnum.BLOCKED.getCode());
            }
        }
        if (cardTypeId != null) {
            queryChain.eq(UserCard::getCardTypeId, cardTypeId);
        }
        if (StringUtils.isNotBlank(channelCode) && StpUtil.hasPermission("channel:view")) {
            queryChain.eq(UserCard::getChannelCode, channelCode);
        }
        if (StringUtils.isNotBlank(sortMap)) {
            String[] s = sortMap.split("_");
            if (s.length == 2) {
                boolean isAsc = s[1].equals("asc");
                queryChain.orderBy(StrUtil.toUnderlineCase(s[0]), isAsc);
            } else {
                queryChain.orderBy(UserCard::getRequestDate, false);
            }
        } else {
            queryChain.orderBy(UserCard::getRequestDate, false);
        }
        // 或者 .field("categories")
        return mapper.paginate(
                paging,
                queryChain,
                fieldQueryBuilder -> fieldQueryBuilder
                        .field(UserCard::getUserCardWallet) // 或者 .field("categories")
                        .queryWrapper(userCard -> QueryWrapper.create()
                                .select().from(USER_CARD_WALLET)
                                .where(USER_CARD_WALLET.USER_ID.eq(userCard.getUserId())).and(USER_CARD_WALLET.CARD_ID.eq(userCard.getCardTypeId())))
        );
    }

    @Override
    public Page<UserCardVo> paging(Page<UserCardVo> paging,
                                   Long userId,
                                   Role role,
                                   Long targetUserId,
                                   String cardNumberLastFour,
                                   String remark,
                                   String startDate,
                                   String endDate,
                                   String cardStatus,
                                   Long id,
                                   Long groupId,
                                   String sortMap) {
        QueryChain<UserCard> queryChain = queryChain();
        queryChain.select(USER_CARD.ALL_COLUMNS);
        queryChain.select(USER.ACCOUNT.as("account"));
        queryChain.select(CARD_GROUP.NAME.as("groupName"));
        queryChain.leftJoin(USER).on(USER_CARD.USER_ID.eq(USER.ID));
        queryChain.leftJoin(CARD_GROUP).on(USER_CARD.CARD_GROUP_ID.eq(CARD_GROUP.ID));
        if (role.getHasAllData()) {
            queryChain.where(USER.PARENT_ID.eq(userId).or(USER.ID.eq(userId)));
            if (RegexUtil.checkObjectIsNotNull(targetUserId)) {
                queryChain.eq(UserCard::getUserId, targetUserId);
            }
        } else {
            queryChain.where(USER_CARD.USER_ID.eq(userId));
        }
        if (RegexUtil.checkStringIsNotNull(cardNumberLastFour)) {
            if (cardNumberLastFour.length() == 4) {
                queryChain.eq(UserCard::getCardNumberLastFour, (cardNumberLastFour));
            } else {
                queryChain.eq(UserCard::getCardNumber, (cardNumberLastFour));
            }
        }
        if (RegexUtil.checkStringIsNotNull(remark)) {
            queryChain.like(UserCard::getRemark, remark);
        }
        if (RegexUtil.checkObjectIsNotNull(startDate)) {
            queryChain.ge(UserCard::getRequestDate, DateUtil.getDayBeginDate(startDate, "yyyy-MM-dd"));
        }
        if (RegexUtil.checkObjectIsNotNull(endDate)) {
            queryChain.le(UserCard::getRequestDate, DateUtil.getDayEndDate(endDate, "yyyy-MM-dd"));
        }
        if (RegexUtil.checkStringIsNotNull(cardStatus)) {
            queryChain.eq(UserCard::getCardStatus, cardStatus);
        } else {
            if (StringUtils.isBlank(cardNumberLastFour)) {
                queryChain.in(UserCard::getCardStatus, CardStatusEnum.ACTIVE.getCode(), CardStatusEnum.BLOCKED.getCode());
            }
        }
        if (RegexUtil.checkObjectIsNotNull(groupId)) {
            queryChain.eq(UserCard::getCardGroupId, groupId);
        }
        if (RegexUtil.checkObjectIsNotNull(id)) {
            queryChain.eq(UserCard::getId, id);
        }
        if (StringUtils.isNotBlank(sortMap)) {
            String[] s = sortMap.split("_");
            if (s.length == 2) {
                boolean isAsc = s[1].equals("asc");
                queryChain.orderBy(StrUtil.toUnderlineCase(s[0]), isAsc);
            } else {
                queryChain.orderBy(UserCard::getRequestDate, false);
            }
        } else {
            queryChain.orderBy(UserCard::getRequestDate, false);
        }
        return this.pageAs(paging, queryChain, UserCardVo.class);
    }

    @Override
    public Page<UserCard> paging(
            Integer pageNumber,
            Integer pageSize,
            Long userId,
            Role role,
            Long targetUserId,
            String cardNumberLastFour,
            String remark,
            String startDate,
            String endDate,
            String cardStatus,
            Long id,
            Long groupId,
            String sortMap) {
        QueryChain<UserCard> queryChain = queryChain();
        queryChain.select(USER_CARD.ALL_COLUMNS);
        queryChain.select(USER.ACCOUNT.as("account"));
        queryChain.leftJoin(USER).on(USER_CARD.USER_ID.eq(USER.ID));
        if (role.getHasAllData()) {
            queryChain.where(USER.PARENT_ID.eq(userId).or(USER.ID.eq(userId)));
        } else {
            queryChain.where(USER_CARD.USER_ID.eq(userId));
        }
        if (RegexUtil.checkStringIsNotNull(cardNumberLastFour)) {
            if (cardNumberLastFour.length() == 4) {
                queryChain.eq(UserCard::getCardNumberLastFour, (cardNumberLastFour));
            } else {
                queryChain.eq(UserCard::getCardNumber, (cardNumberLastFour));
            }
        }
        if (RegexUtil.checkStringIsNotNull(remark)) {
            queryChain.like(UserCard::getRemark, remark);
        }
        if (RegexUtil.checkObjectIsNotNull(startDate)) {
            queryChain.ge(UserCard::getRequestDate, DateUtil.getDayBeginDate(startDate, "yyyy-MM-dd"));
        }
        if (id != null) {
            queryChain.eq(UserCard::getId, id);
        }
        if (RegexUtil.checkObjectIsNotNull(endDate)) {
            queryChain.le(UserCard::getRequestDate, DateUtil.getDayEndDate(endDate, "yyyy-MM-dd"));
        }
        if (RegexUtil.checkStringIsNotNull(cardStatus)) {
            queryChain.eq(UserCard::getCardStatus, cardStatus);
        } else {
            if (StringUtils.isBlank(cardNumberLastFour)) {
                queryChain.in(UserCard::getCardStatus, CardStatusEnum.ACTIVE.getCode(), CardStatusEnum.BLOCKED.getCode());
            }
        }
        if (RegexUtil.checkObjectIsNotNull(groupId)) {
            queryChain.eq(UserCard::getCardGroupId, groupId);
        }
        if (StringUtils.isNotBlank(sortMap)) {
            String[] s = sortMap.split("_");
            if (s.length == 2) {
                boolean isAsc = s[1].equals("asc");
                queryChain.orderBy(StrUtil.toUnderlineCase(s[0]), isAsc);
            } else {
                queryChain.orderBy(UserCard::getRequestDate, false);
            }
        } else {
            queryChain.orderBy(UserCard::getRequestDate, false);
        }
        return this.page(Page.of(pageNumber, pageSize), queryChain);
    }

    @Override
    public Boolean updateCardStatusById(String cardStatus, Long id) {
        return updateChain().set(UserCard::getCardStatus, cardStatus).eq(UserCard::getId, id).update();
    }

    @Override
    public Boolean updateCardStatusByCardNumber(String cardStatus, String cardNumber) {
        return updateChain().set(UserCard::getCardStatus, cardStatus).eq(UserCard::getCardNumber, cardNumber).update();
    }

    @Override
    public Boolean updateCardStatusByChannelCardId(String cardStatus, String channelCardId) {
        return updateChain().set(UserCard::getCardStatus, cardStatus).eq(UserCard::getChannelCardId, channelCardId).update();
    }

    @Override
    public Boolean updateUsableQuotaById(BigDecimal usableQuota, Long id) {
        return updateChain().set(UserCard::getUsableQuota, usableQuota).eq(UserCard::getId, id).update();
    }

    @Override
    public Boolean updateUsableQuotaByCardNumber(BigDecimal usableQuota, String cardNumber) {
        return updateChain().set(UserCard::getUsableQuota, usableQuota).eq(UserCard::getCardNumber, cardNumber).update();
    }

    @Override
    public Boolean updateUsableQuotaByChannelCardId(BigDecimal usableQuota, String channelCardId) {
        return updateChain().set(UserCard::getUsableQuota, usableQuota).eq(UserCard::getChannelCardId, channelCardId).update();
    }

    @Override
    public Boolean updateUsableQuotaAndTotalLimitByChannelCardId(BigDecimal usableQuota, BigDecimal totalLimit, String channelCardId) {
        return updateChain()
                .set(UserCard::getUsableQuota, usableQuota)
                .set(UserCard::getTransactionLimit, totalLimit)
                .eq(UserCard::getChannelCardId, channelCardId)
                .update();
    }

    @Override
    public Boolean updateUsableQuotaAndTotalLimitByChannelCardId(BigDecimal usableQuota, BigDecimal totalLimit, String cardStatus, String channelCardId) {
        return updateChain()
                .set(UserCard::getUsableQuota, usableQuota)
                .set(UserCard::getTransactionLimit, totalLimit)
                .set(UserCard::getCardStatus, cardStatus)
                .eq(UserCard::getChannelCardId, channelCardId)
                .update();
    }

    @Override
    public List<CountData> countById(Long userId) {
        List<CountData> countData = mapper.listByUserId(userId);
        for (int i = 1; i <= 30; i++) {
            Calendar calendar = Calendar.getInstance();
            calendar.add(Calendar.DAY_OF_MONTH, -i);
            String date = DateUtil.format("yyyy-MM-dd", calendar.getTime());
            CountData tempData = countData.stream().filter(it -> it.getDate().equals(date)).findFirst().orElse(null);
            if (tempData == null) {
                countData.add(new CountData(date, 0L));
            }
        }
        return countData.stream().sorted(Comparator.comparing(it -> DateUtil.parse(it.getDate(), "yyyy-MM-dd"))).collect(Collectors.toList());
    }

    @Override
    public List<CountData> countById(Long userId, Date startDate, Date endDate) {
        QueryWrapper query = QueryWrapper.create()
                .select(
                        "DATE_FORMAT(vcc_user_card.request_date, '%Y-%m-%d') as date",
                        "COUNT(vcc_user_card.id) as count"
                )
                .from(UserCard.class)
                .and(UserCard::getRequestDate).between(
                        cn.hutool.core.date.DateUtil.beginOfDay(startDate),
                        cn.hutool.core.date.DateUtil.endOfDay(endDate)
                )
                .groupBy("DATE_FORMAT(request_date, '%Y-%m-%d')")
                .orderBy("date", true); // 按日期降序
        query.eq(UserCard::getUserId, userId);
        return mapper.selectListByQueryAs(query, CountData.class);
    }

    @Override
    public Long countByCardTypeIdAndUserId(Long cardTypeId, Long userId) {
        return queryChain()
                .eq(UserCard::getCardTypeId, cardTypeId)
                .ne(UserCard::getCardStatus, CardStatusEnum.CLOSE.getCode())
                .eq(UserCard::getUserId, userId)
                .count();
    }

    @Override
    public Long countByUserId(Long userId) {
        return queryChain()
                .eq(UserCard::getUserId, userId)
                .ne(UserCard::getCardStatus, CardStatusEnum.CLOSE.getCode())
                .count();
    }

    @Override
    public UserCard getByCardholderIdAndLastFourCardNumber(Long cardholderId, String lastFourCardNumber) {
        return queryChain()
                .eq(UserCard::getCardholderId, cardholderId)
                .eq(UserCard::getCardNumberLastFour, lastFourCardNumber)
                .oneOpt()
                .orElse(null);
    }

    @Override
    public List<UserCard> listByCardholderIdAndLastFourCardNumber(Long cardholderId, String lastFourCardNumber) {
        return queryChain()
                .eq(UserCard::getCardholderId, cardholderId)
                .eq(UserCard::getCardNumberLastFour, lastFourCardNumber)
                .list();
    }

    @Override
    public UserCardStatisticsVo countByUserId(Long userId, Role role) {
        QueryChain<UserCard> queryChain = queryChain();
        queryChain.select(
                "COUNT(vcc_user_card.id) as count",
                //查询激活冻结状态下的可用余额总和
                "IFNULL(SUM(CASE WHEN vcc_user_card.card_status IN ('ACTIVE','BLOCKED') THEN vcc_user_card.usable_quota ELSE 0 END),0) as usableQuotaSum",
                //激活卡数
                "IFNULL(SUM(CASE WHEN vcc_user_card.card_status = 'ACTIVE' THEN 1 ELSE 0 END),0) as activeCount",
                //冻结卡数
                "IFNULL(SUM(CASE WHEN vcc_user_card.card_status = 'BLOCKED' THEN 1 ELSE 0 END),0) as blockedCount",
                //销户卡数
                "IFNULL(SUM(CASE WHEN vcc_user_card.card_status = 'CLOSE' THEN 1 ELSE 0 END),0) as closedCount",
                //风控
                "IFNULL(SUM(CASE WHEN vcc_user_card.card_status = 'CONTROL' THEN 1 ELSE 0 END),0) as controlCount"
        );

        queryChain.leftJoin(USER).on(USER_CARD.USER_ID.eq(USER.ID));
        if (role.getHasAllData()) {
            queryChain.where(USER.PARENT_ID.eq(userId).or(USER.ID.eq(userId)));
        } else {
            queryChain.eq(UserCard::getUserId, userId);
        }
        return queryChain.oneAs(UserCardStatisticsVo.class);
    }

    @Override
    public List<JSONObject> cardTypeStatistics(DateTime startDate, DateTime endDate, Collection<Long> cardTypeIds) {
        QueryChain<UserCard> queryChain = queryChain();
        queryChain.select(
                "vcc_user_card.card_type_id as cardTypeId",
                "COUNT(vcc_user_card.id) as cardCount",
                //查询激活冻结状态下的可用余额总和
                "IFNULL(SUM(CASE WHEN vcc_user_card.card_status IN ('ACTIVE','BLOCKED') THEN vcc_user_card.usable_quota ELSE 0 END),0) as usableQuotaSum",
                //激活卡数
                "IFNULL(SUM(CASE WHEN vcc_user_card.card_status = 'ACTIVE' THEN 1 ELSE 0 END),0) as activeCount",
                //冻结卡数
                "IFNULL(SUM(CASE WHEN vcc_user_card.card_status = 'BLOCKED' THEN 1 ELSE 0 END),0) as blockedCount",
                //销户卡数
                "IFNULL(SUM(CASE WHEN vcc_user_card.card_status = 'CLOSE' THEN 1 ELSE 0 END),0) as closedCount",
                //风控
                "IFNULL(SUM(CASE WHEN vcc_user_card.card_status = 'CONTROL' THEN 1 ELSE 0 END),0) as controlCount"
        );

        if (startDate != null && endDate != null) {
            queryChain.and(USER_CARD.REQUEST_DATE.between(
                    cn.hutool.core.date.DateUtil.beginOfDay(startDate),
                    cn.hutool.core.date.DateUtil.endOfDay(endDate)
            ));
        }
        if (cardTypeIds != null && !cardTypeIds.isEmpty()) {
            queryChain.and(USER_CARD.CARD_TYPE_ID.in(cardTypeIds));
        }
        queryChain.groupBy(USER_CARD.CARD_TYPE_ID);
        return mapper.selectListByQueryAs(queryChain, Row.class).stream().map(JSONObject::new).toList();
    }


    @Override
    public Long countByCardGroupId(Long cardGroupId) {
        return queryChain()
                .eq(UserCard::getCardGroupId, cardGroupId)
                .count();
    }

    @Override
    public Boolean defaultSetGroupId(Long userId, Long targetGroupId) {
        return updateChain()
                .set(UserCard::getCardGroupId, targetGroupId)
                .eq(UserCard::getUserId, userId)
                .eq(UserCard::getCardGroupId, null)
                .update();
    }

    @Override
    public Boolean updateCardGroupId(Long sourceCardGroupId, Long targetCardGroupId) {
        return updateChain()
                .set(UserCard::getCardGroupId, targetCardGroupId)
                .eq(UserCard::getCardGroupId, sourceCardGroupId)
                .update();
    }

    @Override
    public Boolean updateGroupIdIdById(Long id, Long sourceGroupId, Long targetGroupId) {
        return updateChain()
                .set(UserCard::getCardGroupId, targetGroupId)
                .eq(UserCard::getCardGroupId, sourceGroupId)
                .eq(UserCard::getId, id)
                .update();
    }
}
